import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFRichTextString
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.File

/**
 * Author:  LuoHaoLun
 * Email :  506503279@qq.com
 * Date  :  2020/11/2
 */
object ReadManager {

    fun readGuDao(file: File): List<GuDaoExcel> {
        val guDaoNoExcelList = mutableListOf<GuDaoExcel>()
        val workbook = XSSFWorkbook(file)
        for (sheet in workbook.sheetIterator()) {
            sheet as XSSFSheet
            val headers = mutableListOf<List<String>>()
            for (i in 0 until 5) {
                val header = IntRange(0, 30).map { sheet.get(i, it).replace(" ", "").let { it.replace("\n", "") } }
                headers.add(header)
            }
            val headerIndex = headers.indexOfFirst { it.contains("编号") && it.any { it.contains("起始") } }.takeIf { it >= 0 } ?: "股道表找不到表头字段".error()
            val header = headers[headerIndex]
            val lineTypeIndex = header.indexOfFirst { it == "线别" }.takeIf { it >= 0 } ?: "股道表找不到“线别”字段".error()
            val guDaoNoIndex = header.indexOfFirst { it == "编号" }.takeIf { it >= 0 } ?: "股道表找不到“编号”字段".error()
            val startNoIndex = header.indexOfFirst { it.contains("起始") }.takeIf { it >= 0 } ?: "股道表找不到“起始道岔”字段".error()
            val passNoIndex = header.indexOfFirst { it == "经由道岔" }.takeIf { it >= 0 } ?: "股道表找不到“经由道岔”字段".error()
            val endNoIndex = header.indexOfFirst { it.contains("终止") }.takeIf { it >= 0 } ?: "股道表找不到“终止道岔”字段".error()
            for (i in (headerIndex + 1) until sheet.physicalNumberOfRows) {
                val guDaoNo = sheet.get(i, guDaoNoIndex)
                if (guDaoNo.isEmpty()) continue
                val excel = GuDaoExcel()
                excel.guDaoNo = guDaoNo.replaceGuDaoNo()
                excel.lineType = sheet.get(i, lineTypeIndex).ifEmpty { "股道表编号“$guDaoNo”线别为空".error() }
                excel.startNo = sheet.get(i, startNoIndex).ifEmpty { if (excel.lineType == "正线") "" else "股道表编号“$guDaoNo”起始道岔为空".error() }
                excel.endNo = sheet.get(i, endNoIndex)  //.ifEmpty { if (excel.lineType == "正线") "" else "股道表编号“$guDaoNo”终止道岔为空".error() }
                sheet.get(i, passNoIndex).split("\n", " ", ",", ";", "/", "\\").let { excel.passNoList.addAll(it) }
                guDaoNoExcelList.add(excel)
            }
        }
        workbook.close()
        return guDaoNoExcelList
    }

    fun readTurnout(file: File): List<TurnoutExcel> {
        val turnoutList = mutableListOf<TurnoutExcel>()
        val workbook = XSSFWorkbook(file)
        for (sheet in workbook.sheetIterator()) {
            sheet as XSSFSheet
            val headers = mutableListOf<List<String>>()
            for (i in 0 until 5) {
                val header = IntRange(0, 30).map { sheet.get(i, it).replace(" ", "").let { it.replace("\n", "") } }
                headers.add(header)
            }
            val headerIndex = headers.indexOfFirst { it.contains("编号") && it.contains("线别") }.takeIf { it >= 0 } ?: "道岔表找不到表头字段".error()
            val header = headers[headerIndex]
            val lineTypeIndex = header.indexOfFirst { it == "线别" }.takeIf { it >= 0 } ?: "道岔表找不到“线别”字段".error()
            val turnoutNoIndex = header.indexOfFirst { it == "编号" }.takeIf { it >= 0 } ?: "道岔表找不到“编号”字段".error()
            val directionIndex = header.indexOfFirst { it == "左右开" }.takeIf { it >= 0 } ?: "道岔表找不到“左右开”字段".error()
            val chartNoIndex = header.indexOfFirst { it.contains("扳道器种类") }.takeIf { it >= 0 } ?: "道岔表找不到“设计图号”字段".error()
            val mileageIndex = header.indexOfFirst { it.contains("尖轨尖") }.takeIf { it >= 0 } ?: "道岔表找不到“尖轨尖里程”字段".error()
            val typeIndex = header.indexOfFirst { it.contains("种类") }.takeIf { it >= 0 } ?: "道岔表找不到“种类”字段".error()
            for (i in (headerIndex + 1) until sheet.physicalNumberOfRows) {
                val lineType = sheet.get(i, lineTypeIndex)
                if (lineType.isEmpty()) continue
                val excel = TurnoutExcel()
                excel.turnoutNo = sheet.get(i, turnoutNoIndex).ifEmpty { "道岔表编号有为空".error() }
                excel.direction = sheet.get(i, directionIndex).let {
                    when (it) {
                        "左" -> "1"
                        "右" -> "-1"
                        else -> ""
                    }
                } //.ifEmpty { "道岔表编号“${excel.turnoutNo}”左右开为空".error() }
                excel.chartNo = sheet.get(i, chartNoIndex).split("/").last().replace(" ", "")
                    .ifEmpty { "道岔表编号“${excel.turnoutNo}”设计图号为空".error() }
                excel.mileage = sheet.get(i, mileageIndex)
                excel.type = sheet.get(i, typeIndex)
                turnoutList.add(excel)
            }
        }
        workbook.close()
        return turnoutList
    }

    fun readCurve(file: File): List<CurveExcel> {
        val curveList = mutableListOf<CurveExcel>()
        val workbook = XSSFWorkbook(file)
        for (sheet in workbook.sheetIterator()) {
            sheet as XSSFSheet
            val headers = mutableListOf<List<String>>()
            for (i in 0 until 5) {
                val header = IntRange(0, 30).map { sheet.get(i, it).replace(" ", "").let { it.replace("\n", "") } }
                headers.add(header)
            }
            val headerIndex = headers.indexOfFirst { it.contains("股道编号") }.takeIf { it >= 0 } ?: "曲线表找不到表头字段".error()
            val header = headers[headerIndex]
            val guDaoNoIndex = header.indexOfFirst { it == "股道编号" }.takeIf { it >= 0 } ?: "曲线表找不到“股道编号”字段".error()
            val directionIndex = header.indexOfFirst { it.contains("曲线") }.takeIf { it >= 0 } ?: "曲线表找不到“曲线方向”字段".error()
            val radiusIndex = header.indexOfFirst { it.contains("曲线半") }.takeIf { it >= 0 } ?: "曲线表找不到“曲线半径”字段".error()
            val alphaIndex = header.indexOfFirst { it.contains("转向角") }.takeIf { it >= 0 } ?: "曲线表找不到“转向角”字段".error()
            val ls1Index = header.indexOfFirst { it.contains("起缓和") }.takeIf { it >= 0 } ?: "曲线表找不到“起缓和线长”字段".error()
            val ls2Index = header.indexOfFirst { it.contains("终缓和") }.takeIf { it >= 0 } ?: "曲线表找不到“终缓和线长”字段".error()
            val lenIndex = header.indexOfFirst { it.contains("曲线全") }.takeIf { it >= 0 } ?: "曲线表找不到“曲线全长”字段".error()
            val markIndex = header.indexOfFirst { it.contains("附注") }.takeIf { it >= 0 } ?: "曲线表找不到“附注”字段".error()
            for (i in (headerIndex + 1) until sheet.physicalNumberOfRows) {
                val guDaoNo = sheet.get(i, guDaoNoIndex)
                if (guDaoNo.isEmpty()) continue
                val excel = CurveExcel()
                excel.guDaoNo = guDaoNo.replaceGuDaoNo()
                excel.direction = sheet.get(i, directionIndex).let {
                    when (it) {
                        "左" -> "1"
                        "右" -> "-1"
                        else -> it
                    }
                }
                excel.r = sheet.get(i, radiusIndex).ifEmpty { "曲线表股道编号“${excel.guDaoNo}”曲线半径为空".error() }
                excel.alpha = sheet.get(i, alphaIndex)
                excel.ls1 = sheet.get(i, ls1Index)
                excel.ls2 = sheet.get(i, ls2Index)
                excel.l = sheet.get(i, lenIndex)
                excel.mark = sheet.get(i, markIndex)
                curveList.add(excel)
            }
        }
        workbook.close()
        return curveList
    }
}

private fun String.replaceGuDaoNo(): String {
    return this
        .replace("VIII", "8")
        .replace("III", "3")
        .replace("VII", "7")
        .replace("XII", "12")
        .replace("IV", "4")
        .replace("VI", "6")
        .replace("IX", "9")
        .replace("XI", "11")
        .replace("II", "2")
        .replace("V", "5")
        .replace("X", "10")
        .replace("I", "1")

        .replace("Ⅰ", "1")
        .replace("Ⅱ", "2")
        .replace("Ⅲ", "3")
        .replace("Ⅳ", "4")
        .replace("Ⅴ", "5")
        .replace("Ⅵ", "6")
        .replace("Ⅶ", "7")
        .replace("Ⅷ", "8")
        .replace("Ⅸ", "9")
        .replace("Ⅹ", "10")
        .replace("Ⅺ", "11")
        .replace("Ⅻ", "12")


}

data class GuDaoExcel(
    var lineType: String = "",
    var guDaoNo: String = "",
    var startNo: String = "",
    var endNo: String = "",
    val passNoList: MutableList<String> = mutableListOf()
)

data class TurnoutExcel(
    var turnoutNo: String = "",
    var direction: String = "",
    var chartNo: String = "",
    var mileage: String = "",
    var type: String = ""
)

data class CurveExcel(
    var guDaoNo: String = "",
    var direction: String = "",
    var r: String = "",
    var alpha: String = "",
    var ls1: String = "",
    var ls2: String = "",
    var l: String = "",
    var mark: String = ""
)


fun XSSFSheet.get(row: Int, col: Int): String {
    val xRow = getRow(row) ?: return ""
    val cell = xRow.getCell(col) ?: return ""
    return when (cell.cellType) {
        CellType.STRING -> cell.stringCellValue?.trim() ?: ""
        CellType.NUMERIC -> cell.numericCellValue.kp()
        else -> cell.rawValue?.trim() ?: ""
    }
}

fun XSSFSheet.set(row: Int, column: Int, content: Any) {
    val row = this.getRow(row) ?: this.createRow(row)
    val cell = row.getCell(column) ?: row.createCell(column)
    if (content is XSSFRichTextString)
        cell.setCellValue(content)
    else if (content is Number && content == 0)
        cell.setCellValue("")
    else
        cell.setCellValue(content.toString())
//    cell.setCellStyle(cellStyle)
}

fun Double.kp(num: Int = 5): String {
    return String.format("%.${num}f", this).let {
        var str = it
        if (str.contains('.'))
            while (str.endsWith('0') || str.endsWith('.')) {
                str = str.removeRange(str.length - 1, str.length)
                if (!str.contains('.'))
                    break
            }
        str
    }
}